import re
import sqlite3
from datetime import datetime
import time
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Alignment, Border, Side, Font  # 修改：新增导入 Border 和 Side
from tqdm import tqdm  # 新增：导入 tqdm 用于进度条

# 新增：定义颜色列表用于区分不同昵称的背景色
color_list = ['FFDD77', 'AADDFF', 'DDFFAA', 'FFAAFF', 'D3D3D3']  # 可根据需要扩展更多颜色

count = 0


def update_nickname_in_db(nickname, timestamp, content):
    print(f"Updating nickname in database: {nickname}")
    # 连接到SQLite数据库（如果不存在则创建）
    conn = sqlite3.connect('dc.db')
    cursor = conn.cursor()
    # 如果表不存在则创建表
    cursor.execute('''CREATE TABLE IF NOT EXISTS diancan (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nickname TEXT,
        realname TEXT,
        content TEXT,
        time INTEGER
    )''')
    # 检查是否存在相同的时间戳
    cursor.execute('SELECT * FROM diancan WHERE time = ? and nickname = ?',
                   (timestamp, nickname))
    if cursor.fetchone() is not None:
        print(f"时间戳 {timestamp} 已存在，跳过")
        # 如果存在，更新内容
        # cursor.execute(
        #     'UPDATE diancan SET content = ? WHERE time = ? and nickname = ?',
        #     (content, timestamp, nickname))
        # conn.commit()
        # return
    else:
        print(f"时间戳 {timestamp} 不存在，插入新记录")
        # 如果不存在，插入新记录
        cursor.execute(
            'INSERT INTO diancan (nickname,time,content) VALUES (?,?,?)',
            (nickname, timestamp, content))

    # 提交事务并关闭连接
    conn.commit()
    conn.close()


def check(time_value):
    conn = sqlite3.connect('diancan.db')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM diancan WHERE time = ?', (time_value, ))
    res = cursor.fetchall()
    # for row in res:
    # print(row)
    conn.close()
    return res


def insert_into_db(name, date, content):
    conn = sqlite3.connect('diancan.db')
    cursor = conn.cursor()
    cursor.execute(
        'INSERT INTO diancan (name, time, diancan) VALUES (?, ?, ?)',
        (name, date, content))
    conn.commit()
    conn.close()


def test():
    global count
    # content
    with open('19460744006@chatroom-就餐群.html', 'r', encoding='utf-8') as file:
        content = file.read()

    # 查找所有class为nickname的p标签中的内容（包含时间）
    name_date_pattern = re.compile(r'<p class="nickname">(.*?)</p>', re.DOTALL)
    name_date_matches = name_date_pattern.findall(content)

    # 提取姓名和日期
    names_dates = []
    for item in name_date_matches:
        # 使用正则表达式分离姓名和日期
        parts = re.split(r'<span.*?>(.*?)</span>', item)
        if len(parts) >= 2:
            name = parts[0].strip()
            date = parts[1].strip()
            names_dates.append((name, date))

    # 查找所有class为content的p标签中的内容
    content_pattern = re.compile(r'<p class="content">(.*?)</p>')
    contents = content_pattern.findall(content)

    # 确保数量一致
    min_length = min(len(names_dates), len(contents))
    result = []
    for i in range(min_length):
        result.append({
            'name': names_dates[i][0],
            'date': names_dates[i][1],
            'content': contents[i].strip()
        })
    # 连接到SQLite数据库（如果不存在则创建）

    conn = sqlite3.connect('dc.db')
    cursor = conn.cursor()
    # 如果表不存在则创建表
    cursor.execute('''CREATE TABLE IF NOT EXISTS diancan (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nickname TEXT,
        realname TEXT,
        content TEXT,
        time INTEGER
    )''')
    # 打印结果
    for entry in result:
        date_str = entry['date']
        dt = datetime.strptime(date_str, "%Y-%m-%d %H:%M:%S")

        # 转换为时间戳（秒）
        timestamp_seconds = int(dt.timestamp())
        # print(f"时间戳（秒）: {timestamp_seconds}")
        # temp = check(timestamp_seconds)
        if timestamp_seconds < 1733052158:
            #     # print(f"时间戳（秒）: {timestamp_seconds} 已存在")
            #     # print(check(timestamp_seconds))
            continue
        # else:

        print(
            f"时间戳（秒）: {time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(timestamp_seconds))}"
        )

        # 插入数据到数据库
        # insert_into_db(entry['name'], timestamp_seconds, entry['content'])
        print(
            f"姓名: {entry['name']}, 日期: {timestamp_seconds}, 内容: {entry['content']}"
        )
        # 更新昵称到数据库
        nickname = entry['name']
        timestamp = timestamp_seconds
        content = entry['content']
        # update_nickname_in_db(entry['name'], timestamp_seconds,
        #                       entry['content'])
        print(f"Updating nickname in database: {nickname}")

        # 检查是否存在相同的时间戳
        cursor.execute(
            'SELECT id FROM diancan WHERE time = ? and nickname = ?',
            (timestamp, nickname))
        if cursor.fetchone() is not None:
            print(f" {timestamp}  {nickname} 已存在，跳过")
        else:
            count += 1
            print(f"{count}--------时间戳 {timestamp} {nickname} 已存在，跳过")
            # 如果不存在，插入新记录
            # cursor.execute(
            #     'INSERT INTO diancan (nickname,time,content) VALUES (?,?,?)',
            #     (nickname, timestamp, content))
            # update_nickname_in_db(entry['name'])
            # 提交事务并关闭连接
    conn.commit()
    conn.close()


def tongji(start_date=None, end_date=None):
    conn = sqlite3.connect('dc.db')
    cursor = conn.cursor()

    # 查询 nickname、content 和 time 字段
    cursor.execute(
        'SELECT nickname, content, time FROM diancan WHERE time >= ? AND time <= ? ORDER BY time ASC',
        (start_date, end_date))
    rows = cursor.fetchall()
    conn.close()

    stats = {}
    for nickname, content, timestamp in rows:
        if nickname not in stats:
            stats[nickname] = {}
        if content not in stats[nickname]:
            stats[nickname][content] = []
        # 记录完整时间
        readable_time = time.strftime('%Y-%m-%d %H:%M:%S',
                                      time.localtime(timestamp))
        stats[nickname][content].append(readable_time)

    wb = Workbook()
    ws = wb.active
    ws.title = "统计数据"
    ws.append(['名称', '内容', '次数', '时间'])

    # 新增：记录昵称起始行
    nickname_start_row = 2  # 数据从第2行开始
    current_nickname = None
    current_color_index = 0  # 初始化 current_color_index
    nickname_ranges = []  # 存储所有待合并的昵称区间

    # 新增：记录内容起始行
    content_start_row = 2
    current_content = None
    content_ranges = []  # 存储所有待合并的内容区间

    # 新增：定义边框样式
    border_style = Border(left=Side(style='thin'),
                          right=Side(style='thin'),
                          top=Side(style='thin'),
                          bottom=Side(style='thin'))

    # 新增：为主循环添加进度条
    with tqdm(total=len(stats.items()), desc="总体进度", unit="item") as pbar_main:
        for nickname, contents in stats.items():
            with tqdm(total=len(contents.items()),
                      leave=False,
                      desc="子任务",
                      unit="sub") as pbar_sub:
                for content, times in contents.items():
                    # 当检测到昵称变化时，记录上一个昵称的合并范围
                    if current_nickname != nickname:
                        if current_nickname is not None:
                            nickname_ranges.append(
                                (nickname_start_row, ws.max_row))
                        # 开始新的昵称区间
                        nickname_start_row = ws.max_row + 1
                        current_nickname = nickname

                    # 每个时间单独成行
                    for i, time_str in enumerate(times):
                        # 第一条数据保持原有内容
                        if i == 0:
                            ws.append(
                                [nickname, content,
                                 len(times), time_str])  # 修改：将计数列设为时间条目总数
                        else:
                            ws.append(['', content, '', time_str])

                        # 新增：为当前行的内容列（B、C、D列）添加外侧边框
                        for col in range(2, 5):  # 遍历 B、C、D 列
                            ws.cell(row=ws.max_row,
                                    column=col).border = border_style

                        # 检查内容变化并记录合并范围（移到这里以确保顺序）
                        if current_content != content:
                            if current_content is not None:
                                content_ranges.append(
                                    (content_start_row, ws.max_row - 1))
                            content_start_row = ws.max_row
                            current_content = content

                    # 更新内容计数为实际的时间数量
                    if len(times) > 0:
                        # 合并除首行外的昵称单元格
                        if len(times) > 1:
                            ws.merge_cells(start_row=ws.max_row - len(times) +
                                           1,
                                           start_column=1,
                                           end_row=ws.max_row,
                                           end_column=1)

                        # 新增：同步合并内容列和计数列，使用昵称列的范围
                        ws.merge_cells(start_row=ws.max_row - len(times) + 1,
                                       start_column=2,
                                       end_row=ws.max_row,
                                       end_column=2)
                        ws.merge_cells(start_row=ws.max_row - len(times) + 1,
                                       start_column=3,
                                       end_row=ws.max_row,
                                       end_column=3)

                    pbar_sub.update(1)  # 更新子进度条
            pbar_main.update(1)  # 更新主进度条

    # 添加最后一个昵称和内容的合并范围
    if current_nickname is not None:
        nickname_ranges.append((nickname_start_row, ws.max_row))
    if current_content is not None:
        content_ranges.append((content_start_row, ws.max_row))

    # 执行所有昵称列合并操作
    for start_row, end_row in nickname_ranges:
        if start_row <= end_row:  # 确保合法的合并范围
            ws.merge_cells(start_row=start_row,
                           start_column=1,
                           end_row=end_row,
                           end_column=1)

            # 新增：为合并的昵称区域填充背景色
            fill = PatternFill(
                start_color=color_list[current_color_index % len(color_list)],
                end_color=color_list[current_color_index % len(color_list)],
                fill_type="solid")
            # 新增：设置字体大小为20
            nickname_font = Font(size=20)
            for row in ws.iter_rows(min_row=start_row,
                                    max_row=end_row,
                                    min_col=1,
                                    max_col=4):
                for cell in row:
                    cell.fill = fill
                    if cell.column == 1:  # 名字列
                        cell.font = nickname_font
                        # 修改垂直对齐方式为靠上
                        cell.alignment = Alignment(vertical='top',
                                                   horizontal='center')
            current_color_index += 1  # 切换到下一个颜色

    # 新增：设置所有内容垂直居中显示
    for row in ws.iter_rows():
        for cell in row:
            cell.alignment = Alignment(vertical='center')

    # 新增：自动调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter  # 获取列的字母标识（如A、B等）

        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass

        adjusted_width = (max_length + 2)  # 添加额外的宽度以确保留有空间
        ws.column_dimensions[column].width = adjusted_width

    # 新增：设置自动换行和行高
    for row in ws.iter_rows(min_col=4, max_col=4):  # 只遍历D列
        for cell in row:
            cell.alignment = Alignment(wrap_text=True)

    # 修复：正确获取单元格内容并计算行高
    for row in ws.iter_rows(min_row=2):  # 从第2行开始遍历
        times_str = row[3].value  # 获取D列的时间列表
        num_times = len(times_str.split('\n')) if times_str else 0
        ws.row_dimensions[row[0].row].height = 15 * num_times  # 设置行高
    print("保存文件中...")
    # 保存 Excel 文件
    wb.save(
        f"统计数据-{time.strftime('%Y%m%d %H%M%S',time.localtime(start_date))}-{time.strftime('%Y%m%d %H%M%S',time.localtime(end_date))}.xlsx"
    )
    print(
        f"统计数据已导出至 统计数据-{time.strftime('%Y%m%d %H%M%S',time.localtime(start_date))}-{time.strftime('%Y%m%d %H%M%S',time.localtime(end_date))}.xlsx"
    )


if __name__ == '__main__':
    tongji(1735644158, 1751368958)
